//
//  DataBaseHandle.m
//  UI18_SQLite数据库
//
//  Created by 戎耀 on 15-8-5.
//  Copyright (c) 2015年 lanou3g.com 蓝鸥科技. All rights reserved.
//

#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "CollcetModel.h"

@implementation DataBaseHandle


/* 创建单例方法,注意是类方法(+) */
+ (instancetype)shareDataBaseHandle
{
    /*
     * static 修饰词作用 1.在静态区开空间,程序退出时,内存才释放. 2.只初始化一次.
     */
    static DataBaseHandle *databaseHandle = nil;
    
    if (databaseHandle == nil) {
        databaseHandle = [[DataBaseHandle alloc] init];
        
    }
    return databaseHandle;
    
}

/* 创建一个数据库对象 */
static sqlite3 *db;



/* 打开数据库 */
- (void)open
{
    
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    
    //数据库文件路径
    NSString *sqlPath = [docPath stringByAppendingPathComponent:@"students.sqlite"];
    /* 如果数据库已经打开,直接返回,不运行以下代码 */
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    
    
    /* 核心的API : sqlite3_open 函数 */
    int result = sqlite3_open(sqlPath.UTF8String, &db);
    
    /* 根据函数返回值,判断操作是否正确 */
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else{
        NSLog(@"数据库打开失败");
    }
    
    
    
}

/* 创建表单 */
- (void)createTable
{
    /* 创建SQL语句*/
    NSString *createSQL = @"CREATE TABLE IF NOT EXISTS collect(number integer PRIMARY KEY AUTOINCREMENT, title TEXT, url TEXT, newsImage TEXT)";
    
    
    
    /* 核心的API */
    int result = sqlite3_exec(db, createSQL.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"创建表格成功");
    }else
        NSLog(@"创建表格失败");
    
  
}
/* 插入数据 */
- (void)insertCollect:(CollcetModel *)model
{
    /* 创建SQL语句 */
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO collect(title, url, newsImage) VALUES ('%@', '%@', '%@')", model.title,model.url,model.newsImage];
    
    /* 核心API:sqlite3_exec函数 */
    int result =  sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"插入数据成功");
        
    }else
        NSLog(@"插入数据失败");
    
}



/* 删除数据 */
- (void)deleteCollect:(NSString *)title
{
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM collect WHERE title = '%@'", title];
    
    int result = sqlite3_exec(db, deleteSQL.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"删除成功");
    }else
        NSLog(@"删除失败");
    
}

/* 关闭数据库 */
- (void)close
{
    int result = sqlite3_close(db);
    if (SQLITE_OK == result) {
        /* 将db指针指向空 */
        db = nil;
        NSLog(@"关闭数据库成功");
        
    }else
        NSLog(@"关闭数据库失败");
}



/* 查询信息 */
- (NSArray *)selecttCollectWithAll
{
    /* 创建数组,存放符合查询条件的对象 */
    NSMutableArray *array = [NSMutableArray array];
    
    /* 创建SQL查询语句 */
    NSString *selectSQL = @"SELECT * FROM collect";

    
    /* 核心API: */
    /* 准备好的SQL语句对象 */
    sqlite3_stmt *stmt = nil;
    
    /* 编译SQL语句 */
   int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    if (SQLITE_OK == result) {
        NSLog(@"查询成功");
        
        
       /* sqlite3_step(stmt);
        *  执行SQL语句
        * 此函数的返回值为 SQLITE_ROW时,说明还有row(行)可以查询
        
        */
        /* 循环体 */
        while (SQLITE_ROW == sqlite3_step(stmt)) {
        /* 当需要对查询之后的结果进行处理操作时,使用sqlite3_column_函数族 */
        const unsigned char *title = sqlite3_column_text(stmt, 1);/* 取出名字 */
        const unsigned char *url = sqlite3_column_text(stmt, 2);/* 取出url */
        const unsigned char *newsImage = sqlite3_column_text(stmt, 3);/* 取出newsImage */
        /* 创建MOdel对象,赋值 */
            CollcetModel *collect = [[CollcetModel alloc] init];
            collect.title = [NSString stringWithUTF8String:(const char *)title];
            collect.url = [NSString stringWithUTF8String:(const char *)url];
            collect.newsImage = [NSString stringWithUTF8String:(const char *)newsImage];
            
        /* model对象添加到数组中 */
            [array addObject:collect];            
            
        }
        
        /* 销毁 准备好的SQL语句对象 */
        sqlite3_finalize(stmt);
    }else{
        NSLog(@"查询失败");
        sqlite3_finalize(stmt);
    }
    
    return array;
    
    
}



@end
